import database.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
/**
 *
 * @author karolos
 */
//RESTAURANT GUIDE
public class DBManager {

    final private String database = "restaurant";
    final private String user = "pli24";
    final private String password = "pli24";
    final private String driver = "org.apache.derby.jdbc.ClientDriver";
    final private String connString = "jdbc:derby://localhost:1527/";
    private Connection connection;
    private Statement statement;
    private PreparedStatement storeRestaurant;
    private PreparedStatement showAreas;
    private PreparedStatement showPriceRange;
    private PreparedStatement showCategory;
    private PreparedStatement getcatid;
    private PreparedStatement getpriceid;
    private PreparedStatement getareaid;
    private PreparedStatement getrestaurants;
    private PreparedStatement getrestaurantfromtable;
    private PreparedStatement updaterestaurant;
    private PreparedStatement deleterestaurant;
    private PreparedStatement checkadminlogin;
    private PreparedStatement checkuserlogin;
    private PreparedStatement searchrestaurants;
    private PreparedStatement getrestaurantsforeval;
    private PreparedStatement getrestid;
    private PreparedStatement checkeval;
    private PreparedStatement geteval;
    private PreparedStatement createeval;
    private PreparedStatement updateeval;
    private PreparedStatement getallrestaurants;
    private PreparedStatement getallusers;
    private PreparedStatement insertreservation;

//constructor kai sql statements
    public DBManager() {
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(connString + database, user, password);
            statement = connection.createStatement();

            showPriceRange = connection.prepareStatement("SELECT \"range\" FROM APP.PRICERANGE");

            showAreas = connection.prepareStatement("SELECT \"areaname\" FROM APP.AREA");

            showCategory = connection.prepareStatement("SELECT \"categoryname\" FROM APP.CATEGORY");

            storeRestaurant = connection.prepareStatement("INSERT INTO APP.RESTAURANT"
                    + "(\"name\",\"address\",\"phonenumber\",\"info\",\"description\",\"categoryid\",\"pricerangeid\","
                    + "\"areaid\",PHOTOS)VALUES"
                    + "(?,?,?,?,?,?,?,?,?)");

            getcatid = connection.prepareStatement("SELECT \"id\" FROM APP.CATEGORY WHERE \"categoryname\"=?");

            getpriceid = connection.prepareStatement("SELECT \"id\" FROM APP.PRICERANGE WHERE \"range\"=?");

            getareaid = connection.prepareStatement("SELECT \"id\" FROM APP.AREA WHERE \"areaname\"=?");
            //emfanisi opos sto jtable

            getrestaurants = connection.prepareStatement("SELECT APP.RESTAURANT.\"name\","
                    + "APP.RESTAURANT.\"address\",APP.AREA.\"areaname\",APP.RESTAURANT.\"phonenumber\","
                    + "APP.CATEGORY.\"categoryname\"FROM APP.RESTAURANT JOIN APP.AREA "
                    + "ON APP.RESTAURANT.\"areaid\"=APP.AREA.\"id\" JOIN APP.CATEGORY"
                    + " ON APP.RESTAURANT.\"categoryid\"=APP.CATEGORY.\"id\" ORDER BY APP.AREA.\"areaname\"");

            //epistrofi resultset apo ta estiatoria ton jtables
            getrestaurantfromtable = connection.prepareStatement("SELECT \"name\",\"address\","
                    + "\"areaname\",\"phonenumber\",\"categoryname\",\"info\",\"description\","
                    + "\"range\",PHOTOS FROM APP.RESTAURANT  JOIN APP.AREA ON APP.RESTAURANT.\"areaid\" "
                    + "=APP.AREA.\"id\" JOIN APP.CATEGORY ON APP.RESTAURANT.\"categoryid\""
                    + "=APP.CATEGORY.\"id\" JOIN APP.PRICERANGE ON APP.RESTAURANT.\"pricerangeid\""
                    + "=APP.PRICERANGE.\"id\" WHERE \"name\"=? AND \"address\"=? AND \"areaname\""
                    + "=? AND \"phonenumber\"=? AND \"categoryname\"=? ORDER BY APP.AREA."
                    + "\"areaname\"");
            //tropopoihsh estiatoriou
            updaterestaurant = connection.prepareStatement("UPDATE APP.RESTAURANT SET  \"name\""
                    + "=?,\"address\"=?,\"phonenumber\"=?,\"info\"=?,\"description\""
                    + "=?,\"categoryid\"=?,\"pricerangeid\"=?,\"areaid\"=?,PHOTOS=? WHERE \"name\""
                    + "=? AND \"address\"=? AND \"phonenumber\"=? AND \"info\""
                    + "=? AND \"description\"=? AND \"categoryid\"=? AND \"pricerangeid\""
                    + "=? AND \"areaid\"=?");
            //diagrafi estiatoriou
            deleterestaurant = connection.prepareStatement("DELETE FROM APP.RESTAURANT WHERE \"name\"=? "
                    + "AND \"address\"=? AND \"phonenumber\"=?");

            //elegxos an o xristis pou kanei login einai diaxeiristis
            checkadminlogin = connection.prepareStatement("SELECT * FROM APP.ADMIN");

            //elegxos an o xristis pou kanei login einai egegramenos xristis
            checkuserlogin = connection.prepareStatement("SELECT \"username\",\"password\" FROM APP.USERS");
              
            //anazitisi estiatorion me basi ta kritiria anazitisis
            searchrestaurants=connection.prepareStatement("SELECT \"name\",\"address\",\"areaname\",\"phonenumber\",\"categoryname\" "
                    + "FROM APP.RESTAURANT JOIN APP.AREA ON APP.RESTAURANT.\"areaid\"=APP.AREA.\"id\""
                    + "JOIN APP.CATEGORY ON APP.RESTAURANT.\"categoryid\"=APP.CATEGORY.\"id\""
                    + "JOIN APP.PRICERANGE ON APP.RESTAURANT.\"pricerangeid\"=APP.PRICERANGE.\"id\" "
                    + "WHERE \"name\" LIKE ? AND \"areaname\" LIKE ? "
                    + "AND \"categoryname\" LIKE ? AND \"range\" LIKE ?");
            //emfanisi axioloxisis estiatoriou
            getrestaurantsforeval=connection.prepareStatement("SELECT \"quality\",\"prices\",\"servicetime\""
                    + "FROM APP.RESTAURANT JOIN APP.AREA ON APP.RESTAURANT.\"areaid\"=APP.AREA.\"id\""
                    + "JOIN APP.CATEGORY ON APP.RESTAURANT.\"categoryid\"=APP.CATEGORY.\"id\""
                    + "JOIN APP.PRICERANGE ON APP.RESTAURANT.\"pricerangeid\"=APP.PRICERANGE.\"id\""
                    + "JOIN APP.EVALUATION ON APP.RESTAURANT.\"id\"=APP.EVALUATION.\"restaurantid\""
                    + "WHERE \"name\"=? AND \"address\"=? AND \"areaname\"=? "
                    + "AND \"phonenumber\"=? AND \"categoryname\"=?");
            getrestid = connection.prepareStatement("SELECT APP.RESTAURANT.\"id\" FROM APP.RESTAURANT "
                    + "JOIN APP.AREA ON APP.RESTAURANT.\"areaid\"=APP.AREA.\"id\" "
                    + "JOIN APP.CATEGORY ON APP.RESTAURANT.\"categoryid\"=APP.CATEGORY.\"id\" "
                    + "WHERE \"name\"=? AND \"address\"=? AND \"areaname\"=? AND \"phonenumber\"=? "
                    + "AND \"categoryname\"=?");
            checkeval=connection.prepareStatement("SELECT  DISTINCT \"restaurantid\", \"userusername\" "
                    + "FROM APP.RESTAURANT JOIN APP.EVALUATION ON "
                    + "APP.RESTAURANT.\"id\"=APP.EVALUATION.\"restaurantid\" "
                    + "WHERE \"userusername\"=? and \"restaurantid\"=?");

            geteval=connection.prepareStatement("SELECT \"quality\",\"prices\",\"servicetime\","
                    + "\"restaurantid\",\"userusername\" FROM APP.EVALUATION "
                    + "WHERE \"restaurantid\"=? and \"userusername\"=?");

            createeval = connection.prepareStatement("INSERT INTO APP.EVALUATION"
                    + "(\"quality\",\"prices\",\"servicetime\",\"restaurantid\",\"userusername\")VALUES"
                    + "(?,?,?,?,?)");

            updateeval=connection.prepareStatement("UPDATE APP.EVALUATION SET \"quality\"=?,\"prices\"=?,"
                    + "\"servicetime\"=? WHERE \"userusername\"=? AND \"restaurantid\"=? ");
            //epistrofi olon ton estiatorion se arraylist
            getallrestaurants = connection.prepareStatement("SELECT \"name\",\"address\","
                    + "\"areaname\",\"phonenumber\",\"categoryname\",\"info\",\"description\","
                    + "\"range\",PHOTOS FROM APP.RESTAURANT  JOIN APP.AREA ON APP.RESTAURANT.\"areaid\" "
                    + "=APP.AREA.\"id\" JOIN APP.CATEGORY ON APP.RESTAURANT.\"categoryid\""
                    + "=APP.CATEGORY.\"id\" JOIN APP.PRICERANGE ON APP.RESTAURANT.\"pricerangeid\""
                    + "=APP.PRICERANGE.\"id\" ");
            //EPISTROFI OLON TON XRISTON SE ARRAYLIST
            getallusers=connection.prepareStatement("SELECT * FROM APP.USERS");
            
        } catch (Exception exc) {
            System.out.println(exc);
        }
    }

    public ArrayList showAreas() { //emfanisi perioxon kai epistrofi se arraylist
        try {
            ArrayList<String> al = new ArrayList<String>();

            ResultSet rs = showAreas.executeQuery();
            while (rs.next()) {
                al.add(rs.getString("areaname"));

            }
            return al;

        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public ArrayList showPriceRange() { //emfanisi eurous timon kai epistrofi se arraylist
        try {
            ArrayList<String> al = new ArrayList<String>();

            ResultSet rs = showPriceRange.executeQuery();
            while (rs.next()) {
                al.add(rs.getString("range"));

            }
            return al;

        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public ArrayList showCategory() { //emfanisi katigorion kai epistrofi se arraylist
        try {
            ArrayList<String> al = new ArrayList<String>();

            ResultSet rs = showCategory.executeQuery();
            while (rs.next()) {
                al.add(rs.getString("categoryname"));

            }
            return al;

        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public void storeRestaurant(Restaurant r, int cid, int pid, int aid) { //apo8ikeysi estiatoriou sti basi
        try {
            //apo8ikeysi estiatoriou sti basi
            storeRestaurant.setString(1, r.getName());
            storeRestaurant.setString(2, r.getAddress());
            storeRestaurant.setString(3, r.getPhonenumber());
            storeRestaurant.setString(4, r.getInfo());
            storeRestaurant.setString(5, r.getDescription());
            storeRestaurant.setInt(6, cid);
            storeRestaurant.setInt(7, pid);
            storeRestaurant.setInt(8, aid);
            storeRestaurant.setString(9,r.getPhoto());
            storeRestaurant.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public int getcatid(String s) {   //epistrofi id category
        try {
            getcatid.setString(1, s);
            ResultSet rs = getcatid.executeQuery();
            rs.next();
            int catid = rs.getInt("id");
            return catid;
        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }

    public int getpriceid(String s) {   //epistrofi id pricerange
        try {
            getpriceid.setString(1, s);
            ResultSet rs = getpriceid.executeQuery();
            rs.next();
            int pid = rs.getInt("id");
            return pid;
        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }

    public int getareaid(String s) {   //epistrofi id area
        try {
            getareaid.setString(1, s);
            ResultSet rs = getareaid.executeQuery();
            rs.next();
            int aid = rs.getInt("id");
            return aid;
        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }

    //proboli olon ton estiatorion me ta analoga pedia kai epistrofi toy result set
    public ResultSet getrestaurants() throws SQLException {

        ResultSet rs = getrestaurants.executeQuery();
        return rs;
    }
    //proboli estiatorion pano sta pedia tis formas "eigagogi estiatorioy"

    public ResultSet getrestaurantfromtable(String s1, String s2, String s3, String s4, String s5) {
        try {
            getrestaurantfromtable.setString(1, s1);
            getrestaurantfromtable.setString(2, s2);
            getrestaurantfromtable.setString(3, s3);
            getrestaurantfromtable.setString(4, s4);
            getrestaurantfromtable.setString(5, s5);
            ResultSet rs = getrestaurantfromtable.executeQuery();
            return rs;
        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public void updaterestaurant(Restaurant r1, Restaurant r2, int cid1, int pid1, int aid1,
            int cid2, int pid2, int aid2) {
        try {
            updaterestaurant.setString(1, r1.getName());
            updaterestaurant.setString(2, r1.getAddress());
            updaterestaurant.setString(3, r1.getPhonenumber());
            updaterestaurant.setString(4, r1.getInfo());
            updaterestaurant.setString(5, r1.getDescription());
            updaterestaurant.setInt(6, cid1);
            updaterestaurant.setInt(7, pid1);
            updaterestaurant.setInt(8, aid1);
            updaterestaurant.setString(9, r1.getPhoto());
            updaterestaurant.setString(10, r2.getName());
            updaterestaurant.setString(11, r2.getAddress());
            updaterestaurant.setString(12, r2.getPhonenumber());
            updaterestaurant.setString(13, r2.getInfo());
            updaterestaurant.setString(14, r2.getDescription());
            updaterestaurant.setInt(15, cid2);
            updaterestaurant.setInt(16, pid2);
            updaterestaurant.setInt(17, aid2);
            int up = updaterestaurant.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void deleterestaurant(String s1, String s2, String s3) {
        try {
            deleterestaurant.setString(1, s1);
            deleterestaurant.setString(2, s2);
            deleterestaurant.setString(3, s3);
            deleterestaurant.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    //elegxos an o xristis einai admin. h checklogin epistrefei 1 an einai admin

    public int checkadminlogin(String s1, String s2) {
        try {
            int result = 0;
            ResultSet rs = checkadminlogin.executeQuery();
            while (rs.next()) {
                if ((rs.getString("username").equals(s1)) && (rs.getString("password").equals(s2))) {
                    result = 1;
                    break;
                }
                
            }
            return result;
        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;



    }
          //elegxos an o xristis einai egegramenos xristis
    public String checkuserlogin(String s1, String s2) {
        try {
            String result = null;
            ResultSet rs = checkuserlogin.executeQuery();
            while (rs.next()) {
                if ((rs.getString(1).equals(s1)) && (rs.getString(2).equals(s2))) {
                    result = rs.getString(1);
                    break;
                }
                
            }
            return result;
        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
      //anazitisi estiatorion apo xristi
    public ResultSet searchrestaurants(String s1,String s2,String s3,String s4){
        try {
            searchrestaurants.setString(1,"%"+s1+"%");
            searchrestaurants.setString(2,"%"+s2+"%");
            searchrestaurants.setString(3,"%"+s3+"%");
            searchrestaurants.setString(4,"%"+s4+"%");
            ResultSet rs = searchrestaurants.executeQuery();
            return rs;
        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }return null;
    
    }
       //emfanisi estiatorion gia axiologisi
    public ResultSet getrestaurantsforeval(String s1,String s2,String s3,String s4,String s5){
       try {
            getrestaurantsforeval.setString(1, s1);
            getrestaurantsforeval.setString(2, s2);
            getrestaurantsforeval.setString(3, s3);
            getrestaurantsforeval.setString(4, s4);
            getrestaurantsforeval.setString(5, s5);
            ResultSet rs=getrestaurantsforeval.executeQuery();
            return rs;
        } catch (SQLException ex) {
           Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }return null;

    }
          //epistrofi id estiatoriou apo to jtable
    public int getrestid(String s1,String s2,String s3,String s4,String s5){
        try {
            int result = 0;
            getrestid.setString(1, s1);
            getrestid.setString(2, s2);
            getrestid.setString(3, s3);
            getrestid.setString(4, s4);
            getrestid.setString(5, s5);
            ResultSet rs = getrestid.executeQuery();
            rs.next();
            result=rs.getInt(1);
            return result;
        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }return 0;


    }
   public int checkeval(String s1,int id){  //elegxos an exei ginei axiologisi apo ton xristi
        try {
            int count = 0;
            int result = 0;
            checkeval.setString(1, s1);
            checkeval.setInt(2,id);
            ResultSet rs = checkeval.executeQuery();
            while (rs.next()) {
                count++;
            }if (count==1)
                return 1;
        else return 0;
        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }return 0;
}
    //epistrofi axiologisis apo sygekrimeno xristi
     public ResultSet geteval(int i,String user){
        try {
            geteval.setInt(1, i);
            geteval.setString(2, user);
            ResultSet rs = geteval.executeQuery();
            return rs;
        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }return null;

}       //dimiourgia axiologisis
     public void createeval(float quality,float prices,float servicetime,int id,String user){
        try {
            createeval.setFloat(1, quality);
            createeval.setFloat(2, prices);
            createeval.setFloat(3, servicetime);
            createeval.setInt(4, id);
            createeval.setString(5, user);
            createeval.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }
     }
        //update yparxousas axiologisis
      public void updateeval(float quality,float prices,float servicetime,String userusername, int restaurantid) {
        try {
            updateeval.setFloat(1, quality);
            updateeval.setFloat(2, prices);
            updateeval.setFloat(3, servicetime);
            updateeval.setString(4, userusername);
            updateeval.setInt(5, restaurantid);
            int up=updateeval.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

      //epistrofi arraylist me ola ta estiatoria
      public ArrayList getallrestaurants() throws SQLException{
        ArrayList<Restaurant>r =new ArrayList<Restaurant>();
        ResultSet rs=getallrestaurants.executeQuery();
        while(rs.next()){
        Restaurant rest=new Restaurant();
        rest.setName(rs.getString(1));
        rest.setAddress(rs.getString(2));
        rest.setAreaname(rs.getString(3));
        rest.setPhonenumber(rs.getString(4));
        rest.setCategoryname(rs.getString(5));
        rest.setInfo(rs.getString(6));
        rest.setDescription(rs.getString(7));
        rest.setRange(rs.getString(8));
        rest.setPhoto(rs.getString(9));
        r.add(rest);
        }
        return r;
        }
    
      //epistrofi arraylist me olous tous xristes
      public ArrayList getallusers() throws SQLException{
      ArrayList<user>us=new ArrayList<user>();
      ResultSet rs=getallusers.executeQuery();
      while(rs.next()){
      user usr=new user(rs.getString(1));
      us.add(usr);
      }
      return us;
      }

   public void insertreservation(Reservation kratisi) throws SQLException {
       int rid=this.getrestid(kratisi.getRestaurant().getName(),
               kratisi.getRestaurant().getAddress(),
               kratisi.getRestaurant().getAreaname(),
               kratisi.getRestaurant().getPhonenumber(),
               kratisi.getRestaurant().getCategoryname());
       insertreservation.execute("INSERT INTO APP.RESERVATION(\"restaurantid\",\"userusername\") VALUES (?,?)");
       insertreservation.setInt(1, rid);
       insertreservation.setString(2,kratisi.getUser().getUsername());
       insertreservation.executeUpdate();
    }
}



